<?php
class MysqlUtils {
    private static $error;
    private static $pdo;
    private static function init() {
        $db_host = '115.190.74.211';
        $db_port = '3306';
        $db_name = 'coder_nav';
        $db_user = 'root';
        $db_pass = 'coder-nav';
        try {
            self::$pdo = new PDO("mysql:host=$db_host;port=$db_port;dbname=$db_name;charset=UTF8", $db_user, $db_pass);
            self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }

    // 插入
    public static function insert($table, $data) {
        self::init();
        try {
            $columns = implode(', ', array_keys($data));
            $values = implode(', :', array_keys($data));
            $query = "INSERT INTO $table ($columns) VALUES (:$values)";
            $stmt = self::$pdo->prepare($query);
            $stmt->execute($data);
            return self::$pdo->lastInsertId();
        } catch (PDOException $e) {
            return false;
        }
    }

    // 更新
    public static function update($table, $data, $where) {
        self::init();
        try {
            // 构建SET子句
            $set = '';
            foreach ($data as $key => $value) {
                $set .= "$key = :$key, ";
            }
            $set = rtrim($set, ', ');
            // 构建WHERE子句
            $whereClause = '';
            foreach ($where as $key => $value) {
                $whereClause .= "$key = :where_$key AND ";
            }
            $whereClause = rtrim($whereClause, 'AND ');
            // 构建SQL查询
            $query = "UPDATE $table SET $set WHERE $whereClause";
            // 创建预处理语句
            $stmt = self::$pdo->prepare($query);
            // 绑定更新数据的参数
            foreach ($data as $key => $value) {
                $stmt->bindValue(":$key", $value);
            }
            // 绑定WHERE条件的参数
            foreach ($where as $key => $value) {
                $stmt->bindValue(":where_$key", $value);
            }
            // 执行预处理语句
            $stmt->execute();
            // 操作成功
            return true;
        } catch (PDOException $e) {
            return false;
        }
    }

    // 删除
    public static function delete($table, $where, $params = array()) {
        self::init();
        try {
            // 构建WHERE子句
            $whereClause = '';
            foreach ($where as $key => $value) {
                $whereClause .= "$key = :$key AND ";
            }
            $whereClause = rtrim($whereClause, 'AND ');
            // 构建SQL查询
            $query = "DELETE FROM $table WHERE $whereClause";
            // 创建预处理语句
            $stmt = self::$pdo->prepare($query);
            // 绑定WHERE条件的参数
            foreach ($where as $key => $value) {
                $stmt->bindValue(":$key", $value);
            }
            // 执行预处理语句
            $stmt->execute();
            // 操作成功
            return true;
        } catch (PDOException $e) {
            return false;
        }
    }

    // 查询
    public static function select($fields = "*", $table, $conditions = null) {
        $likeConditions = null;
        $orderBy = null;
        $limit = null;
        $params = array();
        self::init();
        try {
            // 构建SELECT子句
            if (is_array($fields)) {
                $fields = implode(', ', $fields);
            } elseif ($fields === "*") {
                $fields = "*";
            } else {
                $fields = "";
            }
            // 构建WHERE子句
            $whereClause = '';
            if (!is_null($conditions) && is_array($conditions)) {
                foreach ($conditions as $key => $value) {
                    $whereClause .= "$key = :$key AND ";
                }
                $whereClause = rtrim($whereClause, 'AND ');
            }
            // 合并LIKE条件
            if (!is_null($likeConditions) && is_array($likeConditions)) {
                if (!empty($whereClause)) {
                    $whereClause .= ' AND ';
                }
                foreach ($likeConditions as $key => $value) {
                    $whereClause .= "$key LIKE :like_$key AND ";
                    $params[":like_$key"] = $value;
                }
                $whereClause = rtrim($whereClause, 'AND ');
            }
            // 构建ORDER BY子句
            $orderByClause = '';
            if (!is_null($orderBy) && is_array($orderBy)) {
                $orderByClause = "ORDER BY " . implode(', ', $orderBy);
            }
            // 构建LIMIT子句
            $limitClause = '';
            if (!is_null($limit)) {
                $limitClause = "LIMIT $limit";
            }
            // 构建SQL查询
            $query = "SELECT $fields FROM $table";
            if (!empty($whereClause)) {
                $query .= " WHERE $whereClause";
            }
            if (!empty($orderByClause)) {
                $query .= " $orderByClause";
            }
            if (!empty($limitClause)) {
                $query .= " $limitClause";
            }
            // 创建预处理语句
            $stmt = self::$pdo->prepare($query);
            // 绑定参数
            if (!is_null($conditions) && is_array($conditions)) {
                foreach ($conditions as $key => $value) {
                    $stmt->bindValue(":$key", $value);
                }
            }
            if (!is_null($likeConditions) && is_array($likeConditions)) {
                foreach ($likeConditions as $key => $value) {
                    $stmt->bindValue(":like_$key", $value);
                }
            }
            // 执行预处理语句
            $stmt->execute();
            // 获取查询结果
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            if(!empty($result)) {
                foreach($result as $item) {
                    echo json_encode($item);
                }
            }
            return $result; // 返回查询结果数组
        } catch (PDOException $e) {
            return false;
        }
    }

    // 执行原生SQL语句
    public static function execute($query, $params = array()) {
        self::init();
        try {
            // 创建预处理语句
            $stmt = self::$pdo->prepare($query);
            // 绑定参数
            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }
            // 执行预处理语句
            $stmt->execute();
            // 操作成功
            return true;
        } catch (PDOException $e) {
            return false;
        }
    }

}